I'm trying to convert the data of a specific column in a text file, during a SSIS process. The column in question contains "numeric" values or an empty space or null value or "--" (two dashes). I want to "replace" the empty space or "--" with a null value (DT_I4) and convert the numeric value to an integer (DT-I4) since the column in the data base is an int field. This is the code that I have in the expression field civilianYears == "--" || civilianYears == " " ? NULL(DT_I4) : (DT_I4)civilianYears However, when I run the task it keeps giving me an error message: The "Derived Column" failed because error code 0xC0049063 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[New]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. I have a feeling it's the conversion in the "true" and "false" part but I can't figure out what it is. Any assistance would be greatly appreciated

Try this: (DT_I4)(civilianYears == "--" || civilianYears == " " ? NULL(DT_I4) : (DT_I4)civilianYears)

That's not working, the expression is not being accepted. It's staying red.

That's odd. I have tested it on my machine and my expression was accepted (it's in black with data type int as output)

My mistake... It was accepted However, I'm getting this error (hex number is different) The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Derived Column 1]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Are there any other errors before this one? "There may be error messages posted before this with more information about the failure." indicates otherwise. The SSIS reference says the following about the error code: "An error occurred while attempting to perform a type cast." Are you 100% sure all data can be converted to integer? MCTS, MCITP - Please mark posts as answered where appropriate.

Here is what is showing in the output window SSIS package "C:\Users\Administrator\documents\visual studio 2010\projects\TLMS\TLMS\Package.dtsx" starting. Information: 0x4004300A at DFT - ExternalTraining, SSIS.Pipeline: Validation phase is beginning. Information: 0x4004300A at DFT - ExternalTraining, SSIS.Pipeline: Validation phase is beginning. Information: 0x40043006 at DFT - ExternalTraining, SSIS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at DFT - ExternalTraining, SSIS.Pipeline: Pre-Execute phase is beginning. Information: 0x402090DC at DFT - ExternalTraining, Flat File Source [12]: The processing of file "C:\Users\Administrator\Desktop\.txt" has started. Information: 0x4004300C at DFT - ExternalTraining, SSIS.Pipeline: Execute phase is beginning. Error: 0xC0049064 at DFT - ExternalTraining, Derived Column [2]: An error occurred while attempting to perform a type cast. Error: 0xC0209029 at DFT - ExternalTraining, Derived Column [2]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Derived Column 1]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Error: 0xC0047022 at DFT - ExternalTraining, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (2) failed with error code 0xC0209029 while processing input "Derived Column Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. Error: 0xC02020C4 at DFT - ExternalTraining, Flat File Source [12]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. Error: 0xC0047038 at DFT - ExternalTraining, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. Information: 0x40043008 at DFT - ExternalTraining, SSIS.Pipeline: Post Execute phase is beginning. Information: 0x402090DD at DFT - ExternalTraining, Flat File Source [12]: The processing of file "C:\Users\Administrator\Desktop\tlms_datamart_20120817\tlms_datamart_ExternalTraining.txt" has ended. Information: 0x4004300B at DFT - ExternalTraining, SSIS.Pipeline: "OLE DB Destination" wrote 0 rows. Information: 0x40043009 at DFT - ExternalTraining, SSIS.Pipeline: Cleanup phase is beginning. Task failed: DFT - ExternalTraining Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "C:\Users\Administrator\documents\visual studio 2010\projects\Package.dtsx" finished: Failure.

This is the culprit: Error: 0xC0049064 at DFT - ExternalTraining, Derived Column [2]: An error occurred while attempting to perform a type cast. Some strings apparently can't be converted to integers. Add some error handling to your dataflow and route the error rows to a flat file, so that you can inspect them.MCTS, MCITP - Please mark posts as answered where appropriate.

Figured out the code for checking the empty space rows. Thanks for the help